﻿#include "db.h"

DB::DB()
{

    QCoreApplication::addLibraryPath("./plugins");
    if (QSqlDatabase::contains("qt_sql_default_connection"))
    {

        database = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        // 建立和SQlite数据库的连接
        database = QSqlDatabase::addDatabase("QSQLITE");
        // 设置数据库文件的名字
        if (QFile::exists("SqliteDataBase.db"))
        {
            database.setDatabaseName("SqliteDataBase.db");
            //init();
        }
        else{
            database.setDatabaseName("SqliteDataBase.db");
            init();
        }
    }
}

void DB::init(){

    /*
    * 用于第一次建库建表，导入初始化数据
    */

    if (!database.isOpen())
        openDb();
    // 用于执行sql语句的对象
    QSqlQuery sqlQuery;
    // 构建创建people表的sql语句字符串
    QString createPeopleSql = QString("CREATE TABLE people (\
                                id TEXT PRIMARY KEY NOT NULL, \
                                name TEXT NOT NULL, \
                                sex TEXT NOT NULL, \
                                nation TEXT NOT NULL, \
                                birth TEXT NOT NULL, \
                                address TEXT NOT NULL, \
                                imagePath TEXT NOT NULL \
                                )");
    // 执行sql语句
    if(!execSql(createPeopleSql))
        qDebug() << "Error: Fail to create table. " << sqlQuery.lastError();
    else
        qDebug() << "Table created!";
    QString insertPeopleSql =
            QString("insert into people values(\"431103200008040616\", \"廖俊涵\", \"male\", \"汉\", \"2000.08.04\", \"山东省淄博市周村区电厂路1127号\", \"%1/pic/people_t/ljh.jpg\"),"
                                            " (\"430481200005124536\", \"李奥华\", \"male\", \"汉\", \"2000.05.12\", \"湖南省耒阳市下塘镇葵田村8组\", \"%2/pic/people_t/lah.jpg\"),"
                                            " (\"430903200210290339\", \"龚浩为\", \"male\", \"汉\", \"2002.10.29\", \"湖南省益阳市赫山区长坡路73号\", \"%3/pic/people_t/ghw.jpg\"),"
                                            " (\"433125200003010047\", \"石涵慧\", \"female\", \"苗\", \"2000.03.01\", \"家庭住址\", \"%4/pic/people_t/shh.jpg\"),"
                                            " (\"123456\", \"Administrator\", \"male\", \"汉\", \"1000.00.00\", \"管理员\",  \"%5/pic/people_t/123456.jpg\")"
                                                                                                                ).arg(QDir::currentPath()).arg(QDir::currentPath()).arg(QDir::currentPath()).arg(QDir::currentPath()).arg(QDir::currentPath());
    if(!execSql(insertPeopleSql))
        qDebug() << "Error: Fail to insert into table. " << sqlQuery.lastError();
    else
        qDebug() << "Table created!";


    // 构建创建user表的sql语句字符串
    QString createUserSql = QString("CREATE TABLE user (\
                                id TEXT PRIMARY KEY NOT NULL, \
                                name TEXT NOT NULL, \
                                sex TEXT NOT NULL, \
                                nation TEXT NOT NULL, \
                                birth TEXT NOT NULL, \
                                address TEXT NOT NULL, \
                                imagePath TEXT NOT NULL, \
                                idCardImagePath TEXT NOT NULL, \
                                emotion TEXT, \
                                action TEXT \
                                )");

//     属性说明：
//     imagePath : 存放的是用户在公安数据库中的人脸
//     IdCardImagePath : 存放的是注册时身份证照片
//     emotion : 用户的特殊验证表情，为 1 代表自然，为 2 代表微笑
//     action : 用户的特殊验证动作序列，为 123 时，代表认证序列为第1第2第3个动作。

    // 执行sql语句
    if(!execSql(createUserSql))
        qDebug() << "Error: Fail to create table. " << sqlQuery.lastError();
    else
        qDebug() << "Table created!";
    QString insertUserSql = "";
//            QString("insert into user values(\"431103200008040616\", \"廖俊涵\", \"男\", \"汉\", \"2000.08.04\", \"山东省淄博市周村区电厂路1127号\", "
//                    "\"公安系统的人脸照片.jpg\", \"注册时身份证照片.jpg\", \"2\", \"123\"),"
//                                            "(\"430481200005124536\", \"李奥华\", \"男\", \"汉\", \"2000.05.12\", \"湖南省耒阳市下塘镇葵田村8组\", "
//                    "\"公安系统的人脸照片.jpg\", \"注册时身份证照片.jpg\", \"2\", \"123\"),"
//                                            "(\"430903200210290339\", \"龚浩为\", \"男\", \"汉\", \"2002.10.29\", \"湖南省益阳市赫山区长坡路73号\", "
//                    "\"%1/pic/people_t/ghw.jpg\", \"%2/pic/user_t/430903200210290339.jpg\", \"2\", \"123\")"
//                    ).arg(QDir::currentPath()).arg(QDir::currentPath()) ;

    if(!execSql(insertUserSql))
        qDebug() << "Error: Fail to insert into table. " << sqlQuery.lastError();
    else
        qDebug() << "Table created!";

    // 构建创建login_log表的sql语句字符串
    QString createLogin_logSql = QString("CREATE TABLE login_log (\
                                id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, \
                                userId TEXT NOT NULL, \
                                time TEXT NOT NULL, \
                                result TEXT NOT NULL, \
                                IP TEXT NOT NULL \
                                )");
//     属性说明：
//     id : 恶意日志序列号
//     userId : 用户身份证号
//     time : 用户点击登录时间，例如2023.03.05
//     result : 用户此次登录结果，例如，登录失败，登陆成功，恶意攻击。
//     IP : 用户登录时IP地址，模拟随便写就行，例如172.27.33.205


    // 执行sql语句
    if(!execSql(createLogin_logSql))
        qDebug() << "Error: Fail to create table. " << sqlQuery.lastError();
    else
        qDebug() << "Table created!";
    QString insertLogin_logSql =
            QString("insert into login_log values(\"1\", \"431103200008040616\", \"2023.03.05\", \"恶意攻击\", \"172.27.33.205\")");
    if(!execSql(insertLogin_logSql))
        qDebug() << "Error: Fail to insert into table. " << sqlQuery.lastError();
    else
        qDebug() << "Table created!";

}

bool DB::execSql(QString stringSql){

    /***
    ****执行sql语句
    ****
    ****传入参数：QString类型的sql语句
    ****
    ****返回结果：是否执行成功
    ***/

    if (!database.isOpen())
        openDb();
    QSqlQuery sqlQuery;
    sqlQuery.prepare(stringSql);
    if(!sqlQuery.exec(stringSql))
    {
        qDebug() << "Error: Fail to execute sqlite database. " << sqlQuery.lastError();
        return false;
    }
    else
    {
        return true;
    }
}

//people_t
QMap<QString, QString> DB::queryById(QString userId){

    /***
    ****查询公安数据库中身份信息
    ****
    ****传入参数：用户身份证号
    ****
    ****返回结果：用户数据字典
    ***/

    if (!database.isOpen())
        openDb();
    QSqlQuery sqlQuery;
    QString stringSql = QString("SELECT * FROM people where id = ").append(userId);
    QMap<QString, QString> userInfo;
    if(!sqlQuery.exec(stringSql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            QString id = sqlQuery.value(0).toString();
            userInfo["id"] = id;
            QString name = sqlQuery.value(1).toString();
            userInfo["name"] = name;
            QString sex = sqlQuery.value(2).toString();
            userInfo["sex"] = sex;
            QString nation = sqlQuery.value(3).toString();
            userInfo["nation"] = nation;
            QString birth = sqlQuery.value(4).toString();
            userInfo["birth"] = birth;
            QString address = sqlQuery.value(5).toString();
            userInfo["address"] = address;
            QString imagePath = sqlQuery.value(6).toString();
            userInfo["imagePath"] = imagePath;
            qDebug()<<QString("id:%1    name:%2    age:%3     nation:%4   birth:%5  address:%6   imagePath:%7").arg(id).arg(name).arg(sex).arg(nation).arg(birth).arg(address).arg(imagePath);
        }
        return userInfo;

    }
    return userInfo;
}

//updata User_t
bool DB::updateRegist(QString userId, QString idCardImagePath){

    /***
    ****注册时自动更新后台用户数据表
    ****
    ****传入参数：用户身份证号，用户身份证照片路径
    ****
    ****返回结果：更新结果
    ****
    ****注意：注册时，特殊验证表情和特殊验证动作序列为空值
    ***/

    auto userInfo = queryById(userId);
    if (userInfo.empty())
        return false;
    QString insertUserSql =
            QString("insert into user(id, name, sex, nation, birth, address, imagePath, idCardImagePath)"
                    " values(\"%1\", \"%2\", \"%3\", \"%4\", \"%5\", \"%6\", \"%7\", \"%8\")").
                                       arg(userInfo["id"]).arg(userInfo["name"]).
                                       arg(userInfo["sex"]).arg(userInfo["nation"]).
                                       arg(userInfo["birth"]).arg(userInfo["address"]).
                                       arg(userInfo["imagePath"]).arg(idCardImagePath);
    if(execSql(insertUserSql)){
        return true;
    }
    else{
        return false;
    }
}

//user_t
QMap<QString, QString> DB::queryUserById(QString userId){

    /***
    ****查询公司中用户身份信息
    ****
    ****传入参数：用户身份证号
    ****
    ****返回结果：用户数据字典
    ***/

    if (!database.isOpen())
        openDb();
    QSqlQuery sqlQuery;
    QString stringSql = QString("SELECT * FROM user where id = ").append(userId);
    QMap<QString, QString> userInfo;
    if(!sqlQuery.exec(stringSql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            QString id = sqlQuery.value(0).toString();
            userInfo["id"] = id;
            QString name = sqlQuery.value(1).toString();
            userInfo["name"] = name;
            QString sex = sqlQuery.value(2).toString();
            userInfo["sex"] = sex;
            QString nation = sqlQuery.value(3).toString();
            userInfo["nation"] = nation;
            QString birth = sqlQuery.value(4).toString();
            userInfo["birth"] = birth;
            QString address = sqlQuery.value(5).toString();
            userInfo["address"] = address;
            QString imagePath = sqlQuery.value(6).toString();
            userInfo["imagePath"] = imagePath;
            QString idCardImagePath = sqlQuery.value(7).toString();
            userInfo["idCardImagePath"] = idCardImagePath;
            QString emotion = sqlQuery.value(8).toString();
            userInfo["emotion"] = emotion;
            QString action = sqlQuery.value(9).toString();
            userInfo["action"] = action;

            qDebug()<<QString("id:%1    name:%2    age:%3     nation:%4   birth:%5  address:%6   imagePath:%7    idCardImagePath:%8   emotion:%9    action:%10 \n").
                      arg(id).arg(name).
                      arg(sex).arg(nation).
                      arg(birth).arg(address).
                      arg(imagePath).arg(idCardImagePath).
                      arg(emotion).arg(action);
        }
        return userInfo;
    }
    return userInfo;
}

//查询日志 log_t
QMap<QString, QMap<QString, QString> > DB::querLoginlogByTimeAndId(QString startTime, QString endTime, QString userId)
{
    /***
        ****查找日志
        ****
        ****传入参数：1、初始时间，2、最后时间，3、userId身份证号(都是QString)
        ****输入示例：2000.08.04, 2018.11.10, 12345678910 千万不能输入2000.012.0123这种日期
        ****
        ****返回结果：QMap<日志属性名, 属性对应内容>
    ***/

    QMap<QString, QMap<QString, QString>> logInfo;
    if (!database.isOpen())
        openDb();
    QSqlQuery sqlQuery;
    QString stringSql = QString("SELECT * FROM login_log where userId = %1").arg(userId);
    if(!sqlQuery.exec(stringSql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            QMap<QString, QString> info;
            QString Id = sqlQuery.value(0).toString();
            QString userId = sqlQuery.value(1).toString();
            QString time = sqlQuery.value(2).toString();
            QString result = sqlQuery.value(3).toString();
            QString IP = sqlQuery.value(4).toString();

            auto tem = time.split(".");
            if (tem[1][0] == '0')
                tem[1] = tem[1][1];
            if (tem[2][0] == '0')
                tem[2] = tem[2][1];
            auto year = tem[0].toInt();
            auto month = tem[1].toInt();
            auto day = tem[2].toInt();

            auto sTem = startTime.split(".");
            if (sTem[1][0] == '0')
                sTem[1] = sTem[1][1];
            if (sTem[2][0] == '0')
                sTem[2] = sTem[2][1];
            auto sYear = (sTem[0]).toInt();
            auto sMonth = sTem[1].toInt();
            auto sDay = sTem[2].toInt();

            auto eTem = endTime.split(".");
            if (eTem[1][0] == '0')
                eTem[1] = eTem[1][1];
            if (eTem[2][0] == '0')
                eTem[2] = eTem[2][1];
            auto eYear = eTem[0].toInt();
            auto eMonth = eTem[1].toInt();
            auto eDay = eTem[2].toInt();

            qDebug() << year;
            qDebug() << month;
            qDebug() << day;

            qDebug() << sYear;
            qDebug() << sMonth;
            qDebug() << sDay;

            qDebug() << eYear;
            qDebug() << eMonth;
            qDebug() << eDay;

            if(year >= sYear && year <= eYear && month >= sMonth && month <= eMonth && day >= sDay && day <= eDay){
                info["userId"] = userId;
                info["time"] = time;
                info["result"] = result;
                info["IP"] = IP;
                logInfo[Id] = info;
            }

        }

    }
    return logInfo;
}

//插入新的日志
bool DB::insertLoginlog(QString time, QString id, QString result, QString IP)
{
    /***
        ****插入新日志
        ****
        ****传入参数：1、时间，2、ID，3、人脸结果 4. IP
        ****
        ****返回结果：bool
    ***/

    QString insertLogin_logSql =
            QString("insert into login_log values(NULL, \"%1\", \"%2\", \"%3\", \"%4\")").arg(id).arg(time).arg(result).arg(IP);
    if(!execSql(insertLogin_logSql)){
        qDebug() << "Error: Fail to insert into table. ";
        return false;
    }
    else{
        qDebug() << "Login_log table inserted!";
        return true;
    }
}

bool DB::updateEmotion(QString userId, QString emotion){

    /***
    ****更新用户特殊验证表情
    ****
    ****传入参数：用户身份证号，特殊验证表情代码
    ****
    ****返回结果：更新结果
    ***/

    QString updateSql = QString("update user set emotion = %1 where Id = %2").arg(emotion).arg(userId);
    if (execSql(updateSql))
        return true;
    else
        return false;
}

//获取数据库所有图片信息 user_t
QMap<QVector<QString>, std::shared_ptr<float> > DB::getAllFace(seeta::FaceRecognizer *FR, seeta::FaceLandmarker *FLM5, seeta::FaceDetector *FD)
{
    if (!database.isOpen()) openDb();
    QSqlQuery sqlQuery;
    QString stringSql = QString("SELECT name, sex, nation, idCardImagePath FROM user");

    //人脸map<vector<身份信息>，人脸特征>
    QMap<QVector<QString>, std::shared_ptr<float>> facesMap;
    if(!sqlQuery.exec(stringSql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else{
        while(sqlQuery.next())
        {
            //身份信息vector，内含名字，性别，民族
            QVector<QString> info;
            info.append(sqlQuery.value(0).toString());
            info.append(sqlQuery.value(1).toString());
            info.append(sqlQuery.value(2).toString());
            auto facePath = sqlQuery.value(3).toString();
            try {

                //打印注册人脸库中人脸图像存放路径
                qDebug() << facePath;

                cv::Mat face = cv::imread((facePath).toStdString());
                seeta::cv::ImageData Tmage = face;
                SeetaFaceInfoArray faces2 = FD->detect(Tmage);
                std::vector<SeetaPointF> points2 = FLM5->mark(Tmage, faces2.data[0].pos);
                auto feature2 = extract(FR, Tmage, points2);
                facesMap[info] = feature2;
            } catch (...) {
            }
        }
    }
    return facesMap;
}

//特征值
std::shared_ptr<float> DB::extract(seeta::FaceRecognizer *fr, const seeta::cv::ImageData &image, const std::vector<SeetaPointF> &points)
{
    /***
        ****提取人脸特征
        ****
        ****传入参数：1、人脸识别器指针，2、李奥华自定义ImageData引用 3、5点人脸关键点引用
        ****
        ****返回结果：更新结果
    ***/

    std::shared_ptr<float> features(
        new float[fr->GetExtractFeatureSize()],
        std::default_delete<float[]>());
    fr->Extract(image, points.data(), features.get());
    return features;
}

bool DB::updateAction(QString userId, QString action){

    /***
    ****更新用户特殊验证动作序列
    ****
    ****传入参数：用户身份证号，特殊验证表情代码
    ****
    ****返回结果：更新结果
    ***/

    QString updateSql = QString("update user set action = %1 where Id = %2").arg(action).arg(userId);
    if (execSql(updateSql))
        return true;
    else
        return false;
}

void DB::openDb(){

    if (!database.open())
    {
        qDebug() << "Error: Failed to connect database." << database.lastError();
    }
    else
    {
        // do something
    }
}

void DB::closeDb(){
    database.close();
}

DB::~DB(){
    closeDb();
}
